package database;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.ResultSetMetaData;
import javax.swing.table.DefaultTableModel;

public class test {

    private static final String url = "jdbc:mysql://localhost:3306/test?useSSL=false&serverTimezone=UTC";
    private static final String user = "root";
    private static final String password = "123456";

    public static ResultSet executeQuery(String sql) throws SQLException {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;

        try {
            conn = DriverManager.getConnection(url, user, password);
            pstmt = conn.prepareStatement(sql);
            rs = pstmt.executeQuery();
        } catch (SQLException e) {
            throw new SQLException("Error in executing SQL query: " + e.getMessage(), e);
        } finally {
            // 注意：这里不关闭连接，因为 ResultSet 需要在外部使用时关闭
            // 在实际使用中需要手动关闭 ResultSet、PreparedStatement 和 Connection
        }

        return rs;
    }

    public static void executeUpdate(String sql) throws SQLException {
        Connection conn = null;
        PreparedStatement pstmt = null;

        try {
            conn = DriverManager.getConnection(url, user, password);
            pstmt = conn.prepareStatement(sql);

            // 判断 SQL 类型并执行对应操作
            if (sql.trim().toLowerCase().startsWith("insert")) {
                pstmt.executeUpdate();
                System.out.println("插入成功");
            } else if (sql.trim().toLowerCase().startsWith("update")) {
                int rowsAffected = pstmt.executeUpdate();
                System.out.println("修改成功，修改的行数：" + rowsAffected);
            } else if (sql.trim().toLowerCase().startsWith("delete")) {
                int rowsAffected = pstmt.executeUpdate();
                System.out.println("删除成功，删除的行数： " + rowsAffected);
            } else {
                System.out.println("操作错误！");
            }

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            // 关闭连接和 statement 对象
            try {
                if (pstmt != null) {
                    pstmt.close();
                }
                if (conn != null) {
                    conn.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    public static DefaultTableModel executeQueryAndBuildTableModel (String sql) throws SQLException {
        ResultSet rs = null;
        DefaultTableModel model = null;

        try {
            rs = executeQuery(sql);

            // 从ResultSet获取列名
            ResultSetMetaData metaData = rs.getMetaData();
            int columnCount = metaData.getColumnCount();
            String[] columnNames = new String[columnCount];
            for (int i = 0; i < columnCount; i++) {
                columnNames[i] = metaData.getColumnName(i + 1); // 列索引从1开始
            }

            // 创建数据模型并填充数据
            model = new DefaultTableModel(columnNames, 0);
            while (rs.next()) {
                Object[] row = new Object[columnCount];
                for (int i = 0; i < columnCount; i++) {
                    row[i] = rs.getObject(i + 1); // 行数据索引从1开始
                }
                model.addRow(row);
            }
        } catch (SQLException e) {
            throw new SQLException("Error in executing SQL query and building table model: " + e.getMessage(), e);
        } finally {
            // 关闭 ResultSet
            try {
                if (rs != null) {
                    rs.close();
                }
            } catch (SQLException e) {
                e.printStackTrace(); // 可以根据实际情况选择记录日志或抛出异常
            }
        }

        return model;
    }

   /*public static void main(String[] args) {
        String insertSql = "INSERT INTO tableName (column1, column2) VALUES (?, ?)";
        String updateSql = "UPDATE tableName SET column1 = ? WHERE column2 = ?";
        String deleteSql = "DELETE FROM tableName WHERE column = ?";
        String selectSql = "SELECT * FROM tableName WHERE column = ?";

        try {
            executeUpdate(insertSql);
            executeUpdate(updateSql);
            executeUpdate(deleteSql);

            // 示例查询
            DefaultTableModel model = executeQueryAndBuildTableModel(selectSql);
            // 使用 model 来展示或者做其他处理
        } catch (SQLException e) {
            e.printStackTrace(); // 可以根据实际情况选择记录日志或抛出异常
        }
    }*/
}
